<?php

/**
 * Class to handle all db operations
 * This class will have CRUD methods for database tables
 *
 * @author minhthaind, cuongnq
 */
require_once dirname(__FILE__) . '/UtilApi.php';

class DbHandler {

    private $conn;

    function __construct() {
        require_once dirname(__FILE__) . '/DbConnect.php';
        // opening db connection
        $db = new DbConnect();
        $this->conn = $db->connect();
    }

    /* ------------- `users` table method ------------------ */

    /**
     * Creating new user
     * @param String $name User full name
     * @param String $email User login email id
     * @param String $password User login password
     */
    public function registerUser($email, $password, $api_key) {
        require_once 'PassHash.php';
        require_once '/../Command/SqlUserTable.php';

        // First check if user already existed in db
        if (!$this->isUserExists($email)) {
            // Generating password hash
            $password_hash = PassHash::hash($password);

            // insert query
            $sql = SqlUserTable::SqlRegisterUser();
            $stmt = $this->conn->prepare($sql);
            $stmt->bind_param("sss", $email, $password_hash, $api_key);

            $result = $stmt->execute();

            $stmt->close();

            // Check for successful insertion
            if ($result) {
                // User successfully inserted
                return USER_CREATED_SUCCESSFULLY;
            } else {
                // Failed to create user
                return USER_CREATE_FAILED;
            }
        } else {
            // User with same email already existed in the db
            return USER_ALREADY_EXISTED;
        }

        return true;
    }

    /**
     * @param type $facebookId
     * @param type $email
     * @param type $password
     * @param type $city
     * @param type $photo
     * @param type $longtitude
     * @param type $lattitude
     * @param type $dob
     * @param type $sex
     * @param type $allowAdult
     * @param type $cpf
     * @param type $firstName
     * @param type $lastName
     * @param type $userphoto
     * @return boolean
     */
    public function createUser($facebookId, $email, $password, $city, $photo, $longtitude, $lattitude, $dob, $sex, $allowAdult, $cpf, $firstName, $lastName, $userphoto) {
        require_once 'PassHash.php';
        require_once '/../Command/SqlUserTable.php';

        // First check if user already existed in db
        if (!$this->isFacebookIdExists($facebookId)) {
            if (!$this->isUserExists($email)) {
                // Generating password hash
                $password_hash = PassHash::hash($password);

                // Generating API key
                $utilApi = new UtilApi;
                $api_key = $utilApi->generateApiKey();

                // insert query
                $sql = SqlUserTable::SqlInsertUser();
                $stmt = $this->conn->prepare($sql);
                $stmt->bind_param("ssssssiisssssss", $facebookId, $email, $photo, $city, $longtitude, $lattitude, $dob, $sex, $allowAdult, $cpf, $firstName, $lastName, $password_hash, $userphoto, $api_key);
                $result = $stmt->execute();
                $stmt->close();

                // Check for successful insertion
                if ($result) {
                    // User successfully inserted
                    return USER_CREATED_SUCCESSFULLY;
                } else {
                    // Failed to create user
                    return USER_CREATE_FAILED;
                }
            } else {
                // User with same email already existed in the db
                return USER_ALREADY_EXISTED;
            }
        } else {
            return FACEBOOKID_ALREADY_EXISTED;
        }

        return true;
    }

    /**
     * Checking user login
     * @param String $email User login email id
     * @param String $password User login password
     * @return boolean User login status success/fail
     */
    public function checkLogin($email, $password) {
        // fetching user by email
        require_once dirname(__FILE__) . '/../Command/SqlUserTable.php';
        $sql = SqlUserTable::SqlLogin();
        $stmt = $this->conn->prepare($sql);
        try {
            $stmt->bind_param("s", $email);
            $stmt->execute();
            $stmt->bind_result($user_id, $password_hash);
            $stmt->store_result();

            if ($stmt->num_rows > 0) {
                // Found user with the email
                // Now verify the password
                $stmt->fetch();
                $stmt->close();
                if (PassHash::check_password($password_hash, $password)) {
                    $sql = SqlUserTable::SqlDeleteUserLogin();
                    $stmt = $this->conn->prepare($sql);
                    $stmt->bind_param("i", $user_id);
                    $stmt->execute();
                    $stmt->close();
                    $sql = SqlUserTable::SqlInsertUserLogin();
                    $utilApi = new UtilApi;
                    $token = $utilApi->generateApiKey();
                    $stmt = $this->conn->prepare($sql);
                    $stmt->bind_param("is", $user_id, $token);
                    $stmt->execute();
                    $stmt->close();
                    // User password is correct
                    return TRUE;
                } else {
                    // user password is incorrect
                    return FALSE;
                }
            } else {
                $stmt->close();
                // user not existed with the email
                return FALSE;
            }
        } catch (\Slim\Exception $ex) {
            echo $ex->getMessage();
            $stmt->close();
        }
    }

    public function getUserById($userId) {
        // fetching user by user id
        require_once dirname(__FILE__) . '/../Command/SqlUserTable.php';
        $sql = SqlUserTable::SqlSelectUserById($userId);
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $userId);
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $user = $result->fetch_array(MYSQLI_ASSOC);
            $stmt->close();
            return $user;
        } else {
            return NULL;
        }
    }

    /**
     * Checking for duplicate user by email address
     * @param String $email email to check in db
     * @return boolean
     */
    private function isUserExists($email) {
        $stmt = $this->conn->prepare("SELECT id from user WHERE email = ?");
        $stmt->bind_param("s", $email);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    public function isUserExistsById($userId) {
        $stmt = $this->conn->prepare("SELECT id from user WHERE id = ?");
        $stmt->bind_param("s", $userId);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    /**
     * Checking for duplicate user by email address
     * @param String $email email to check in db
     * @return boolean
     */
    private function isFacebookIdExists($facebookId) {
        require_once dirname(__FILE__) . '/../Command/SqlUserTable.php';
        $sql = SqlUserTable::SqlSelectUserByFacebookId();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $facebookId);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    /**
     * Fetching user by email
     * @param String $email User email id
     */
    public function getUserByEmail($email) {
        require_once dirname(__FILE__) . '/../Command/SqlUserTable.php';
        $sql = SqlUserTable::SqlSelectUser($email);
        $stmt = $this->conn->stmt_init();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $email);
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $user = $result->fetch_array(MYSQLI_ASSOC);
            $stmt->close();
            return $user;
        } else {
            return NULL;
        }
    }

    /**
     * Fetching user api key
     * @param String $user_id user id primary key in user table
     */
    public function getApiKeyById($user_id) {
        $stmt = $this->conn->prepare("SELECT api_key FROM user WHERE id = ?");
        $stmt->bind_param("i", $user_id);
        if ($stmt->execute()) {
            // $api_key = $stmt->get_result()->fetch_assoc();
            // TODO
            $stmt->bind_result($api_key);
            $stmt->close();
            return $api_key;
        } else {
            return NULL;
        }
    }

    /**
     * Fetching user id by api key
     * @param String $api_key user api key
     */
    public function getUserId($api_key) {

        $stmt = $this->conn->prepare("SELECT id FROM user WHERE apiKey = ?");
        $stmt->bind_param("s", $api_key);
        if ($stmt->execute()) {
            $stmt->bind_result($user_id);
            $stmt->fetch();
            // TODO
            // $user_id = $stmt->get_result()->fetch_assoc();
            $stmt->close();
            return $user_id;
        } else {
            return NULL;
        }
    }

    /**
     * Fetching user id by api token
     * @param String $api_token 
     */
    public function getUserIdByToken($api_token) {
        require_once dirname(__FILE__) . '/../Command/SqlUserTable.php';
        $sql = SqlUserTable::SqlGetIdUserbyToken();
        $stmt = $this->conn->prepare($sql);

        $stmt->bind_param("s", $api_token);
        if ($stmt->execute()) {
            $stmt->bind_result($user_id);
            $stmt->fetch();
            // TODO
            // $user_id = $stmt->get_result()->fetch_assoc();
            $stmt->close();

            return $user_id;
        } else {
            return NULL;
        }
    }

    /**
     * Update user location
     * @param int $user_id
     * @param double $longtitude
     * @param double $lattitude
     * @return bolean
     */
    public function updateUserLocation($user_id, $longtitude, $lattitude) {

        require_once dirname(__FILE__) . '/../Command/SqlUserTable.php';

        $sql = SqlUserTable::SqlUpdateUserLocation();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("ssi", $longtitude, $lattitude, $user_id);
        $stmt->execute();
        $num_affected_rows = $stmt->affected_rows;
        $stmt->close();
        return $num_affected_rows > 0;
    }
    
    public function updateUserPhoto($user_id, $urlPhoto) {

        require_once dirname(__FILE__) . '/../Command/SqlUserTable.php';

        $sql = SqlUserTable::SqlUpdateUserPhoto();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("si", $urlPhoto, $user_id);
        $stmt->execute();
        $num_affected_rows = $stmt->affected_rows;
        $stmt->close();
        return $num_affected_rows > 0;
    }

    public function activeUserByToken($token) {

        require_once dirname(__FILE__) . '/../Command/SqlUserTable.php';

        $sql = SqlUserTable::SqlActiveUser();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $token);
        $stmt->execute();
        $num_affected_rows = $stmt->affected_rows;
        $stmt->close();
        return $num_affected_rows > 0;
    }

    /**
     * Validating user api key
     * If the api key is there in db, it is a valid key
     * @param String $api_key user api key
     * @return boolean
     */
    public function isValidApiKey($api_key) {
        //require_once dirname(__FILE__) . '/../Command/SqlUserTable.php';
        //$sql = SqlUserTable::SqlGetIdUserbyToken();
        $stmt = $this->conn->prepare(" SELECT id from user WHERE apiKey = ? ");
        $stmt->bind_param("s", $api_key);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    /**
     * Validating user api key
     * If the api key is there in db, it is a valid key
     * @param String $api_key user api key
     * @return boolean
     */
    public function isValidApiToken($api_token) {
        require_once dirname(__FILE__) . '/../Command/SqlUserTable.php';
        $sql = SqlUserTable::SqlGetIdUserbyToken();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $api_token);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        //var_dump($num_rows);die();
        $stmt->close();
        return $num_rows > 0;
    }

    /**
     * Creating new device
     * @param String $user_id user id to whom task belongs to
     * @param String $token task text
     */
    public function createDevice($user_id, $token, $systemos) {
        require_once dirname(__FILE__) . '/../Command/SqlDeviceTable.php';

        $sql = SqlDeviceTable::SqlInsertDevice();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("sss", $token, $systemos, $user_id);
        $result = $stmt->execute();
        $stmt->close();

        if ($result) {
            return CREATED_SUCCESSFULLY;
        } else {
            // task failed to create
            return CREATE_FAILED;
        }
    }

    /**
     * 
     * @param type $partyId
     * @param type $ownerId
     * @param type $priceCatId
     * @return string
     */
    public function createTicket($partyId, $ownerId, $priceCatId) {
        require_once dirname(__FILE__) . '/../Command/SqlTicketTable.php';

        $utilApi = new UtilApi;
        $ticketCode = $utilApi->generateTicketCode();
        $sql = SqlTicketTable::SqlInsetTicket();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("iiis", $partyId, $ownerId, $priceCatId, $ticketCode);
        $result = $stmt->execute();
        $stmt->close();

        if ($result) {
            return $this->getTicket('ticketCode', $ticketCode);
        } else {
            // task failed to create
            return CREATE_FAILED;
        }
    }

    public function updateTicket($partyId, $ownerId, $priceCatId, $ticketId) {
        require_once dirname(__FILE__) . '/../Command/SqlTicketTable.php';

        $sql = SqlTicketTable::SqlUpdateTicket();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("iiii", $ownerId, $partyId, $priceCatId, $ticketId);
        $result = $stmt->execute();
        $stmt->close();

        if ($result) {
            return $this->getTicket('id', $ticketId);
        } else {
            // task failed to create
            return UPDATE_FAILED;
        }
    }

    public function consumeTicket($ticketCode, $consumeUserId, $receptionistId) {
        require_once dirname(__FILE__) . '/../Command/SqlTicketTable.php';

        $sql = SqlTicketTable::SqlConsumeTicket();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("iis", $consumeUserId, $receptionistId, $ticketCode);
        $result = $stmt->execute();
        $stmt->close();

        if ($result) {
            return $this->getTicket('ticketCode', $ticketCode);
        } else {
            // task failed to create
            return CONSUME_FAILED;
        }
    }

    public function validateTicket($ticketCode) {
        return $this->getTicket(' isConsumed = 1 AND ticketCode ', $ticketCode);
    }

    public function getTicket($nameColumn, $value) {
        require_once dirname(__FILE__) . '/../Command/SqlTicketTable.php';
        $sql = SqlTicketTable::SqlSelectTicket($nameColumn);
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $value);
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $ret = $result->fetch_array(MYSQLI_ASSOC);
            $stmt->close();
            return $ret;
        } else {
            return NULL;
        }
    }

    public function isTicketExits($ticketCode) {
        $ret = $this->getTicket('ticketCode', $ticketCode);
        return $ret != NUll;
    }

    public function isPartyExists($partyId) {
        require_once dirname(__FILE__) . '/../Command/SqlPartyTable.php';
        $sql = SqlPartyTable::SqlSelectPartyById();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $partyId);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    public function isCategoryExists($categoryId) {
        $stmt = $this->conn->prepare("SELECT categories.id FROM categories WHERE categories.id = ?");
        $stmt->bind_param("i", $categoryId);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    public function isTicketCatExists($ticketId) {
        $stmt = $this->conn->prepare("SELECT ticketcat.id FROM ticketcat WHERE ticketcat.id = ? ");
        $stmt->bind_param("i", $ticketId);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    public function isReceptoinistExists($id) {
        require_once dirname(__FILE__) . '/../Command/SqlReceptionistTable.php';
        $where = " WHERE id = ? ";
        $sql = SqlReceptionistTable::SqlSelectReceptionist($where);
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    /**
     * Updating Device
     * @param String $device_id id of the device
     * @param String $token 
     * @param String $systemos
     */
    public function updateDevice($user_id, $token, $systemos, $id) {

        require_once '/../Command/SqlDeviceTable.php';

        $sqlDevice = new SqlDeviceTable();
        $sql = $sqlDevice->SqlUpdateDevice();

        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("ssii", $token, $systemos, $user_id, $id);
        $stmt->execute();
        $num_affected_rows = $stmt->affected_rows;
        $stmt->close();
        return $num_affected_rows > 0;
    }

    /**
     * Get Parties
     * @param String $category id of category
     * @param String $isMostWanted
     * @param String $systemos
     */
    public function getPartiesByCategory($category) {

        require_once '/../Command/SqlPartyTable.php';

        $sqlParty = new SqlPartyTable();
        $sql = $sqlParty->SqlParitesList($category);

        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $category);
        $stmt->execute();
        $parties = $stmt->get_result();

        $stmt->close();
        return $parties;
    }

    /**
     * Get Parties
     * @param String $category id of category
     * @param String $isMostWanted
     * @param String $systemos
     */
    public function getMostWantedParties() {

        require_once '/../Command/SqlPartyTable.php';

        $sqlParty = new SqlPartyTable();
        $sql = $sqlParty->SqlParitesList(null,1);

        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $parties = $stmt->get_result();

        $stmt->close();
        return $parties;
    }

    /**
     * Get Parties Which Next Occur
     */
    public function getNextParties() {

        require_once '/../Command/SqlPartyTable.php';

        $sqlParty = new SqlPartyTable();
        $sql = $sqlParty->SqlGetNextParties();

        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $parties = $stmt->get_result();

        $stmt->close();
        return $parties;
    }

    public function getPromotionParties() {

        require_once '/../Command/SqlPartyTable.php';

        $sqlParty = new SqlPartyTable();
        $sql = $sqlParty->SqlGetPromotionParties();
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $parties = $stmt->get_result();

        $stmt->close();
        return $parties;
    }

    public function getUsersInParty($party_id) {

        require_once '/../Command/SqlPartyTable.php';

        $sqlParty = new SqlPartyTable();
        $sql = $sqlParty->SqlGetUsersInParty($party_id);
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $party_id);
        $stmt->execute();
        $parties = $stmt->get_result();

        $stmt->close();
        return $parties;
    }

    public function getValidPartyCode($party_code) {
      
        require_once '/../Command/SqlPartyTable.php';
        
        $sqlParty = new SqlPartyTable();
        $sql = $sqlParty->SqlValidateGenericCode($party_code);
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("s", $party_code);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    public function getTicketsByUser($user_id) {
        require_once dirname(__FILE__) . '/../Command/SqlPartyTable.php';
        $sql = SqlTicketTable::SqlSelectTicket("onwerID");
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("i", $user_id);
        $stmt->execute();
        $tickets = $stmt->get_result();
        $stmt->close();
        return $tickets;
    }
    public function isTicketByIdExits($ticket_id) {
        $ret = $this->getTicket('id', $ticket_id);
        return $ret != NUll;
    }

    public function createTransfer($from_user, $to_user, $ticket_id) {

        require_once dirname(__FILE__) . '/../Command/SqlTicketTable.php';
        
        $sql = SqlTicketTable::SqlInsetTransference();
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("iii", $from_user, $to_user, $ticket_id);
        $result = $stmt->execute();
        $stmt->close();

        // Check for successful insertion
        if ($result) {
            // User successfully inserted
            return TRANSFER_CREATED_SUCCESSFULLY;
        } else {
            // Failed to create user
            return TRANSFER_CREATED_FAILED;
        }
    }

    public function getCategories() {

        require_once dirname(__FILE__) . '/../Command/SqlCategoryTable.php';

        $sql = SqlCategoryTable::SqlGetCategories();
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->get_result();
        $stmt->close();
        return $result;
    }

    function __destruct() {
        $this->conn = null;
    }

}

?>
